#!usr/bin/python
# -*- coding:utf-8 -*-

"""
单个数据库表的机组运行条数不进行累加
"""
import pymysql
import datetime

# 查询数据库
conn = pymysql.connect(host='xx.x.xx.226', port=3306, user='root', passwd='xxx', db='wolf', charset='utf8')
cursor = conn.cursor()

# 获取数据库中所有表名称
get_table_name = "select table_name from information_schema.tables where table_schema='wolf' and table_name like 'status_water%';"
# 执行
cursor.execute(get_table_name)
# 获取所有的表名称
key_days = cursor.fetchall()

# 插入数据库
input_conn = pymysql.connect(host='xx.x.xx.229', port=3306, user='root', passwd='xxx', db='gprs', charset='utf8')
input_cursor = input_conn.cursor()
drop_table = '''DROP TABLE IF EXISTS day_value'''
input_cursor.execute(drop_table)

create_table = '''
        create table day_value(
        zid INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
        unitId INT NOT NULL,
        imei VARCHAR(64) NOT NULL,
        linkerId INT NOT NULL,
        log_time date NOT NULL,
        status_value INT NOT NULL)ENGINE=innodb DEFAULT CHARSET=utf8;
'''
input_cursor.execute(create_table)
sql = "INSERT INTO day_value(unitId, imei, linkerId, log_time, status_value) VALUES (%s,%s,%s,%s,%s)"


for key_day in key_days[:-1]:
    ltime = datetime.date(int(key_day[0].split('_')[-1][0:4]), int(key_day[0].split('_')[-1][4:6]),
                          int(key_day[0].split('_')[-1][6:8]))

    select_sql2 = "select sw.id as unitId, substring_index(linker.iuid,'-',-1) as imei, linker.id as linkerId, \
                    '{}' as log_time, count(sw.id) as status_value \
                        from {key} sw, \
                        iot_unit unit, \
                        iot_linker linker \
                        where \
                        sw.id=unit.id and unit.linker_id=linker.id group by substring_index(linker.iuid,'-',-1);".format(str(ltime),
                        key=key_day[0])

    cursor.execute(select_sql2)
    result = cursor.fetchall()

    # 批量插入
    try:
        input_cursor.executemany(sql, result)
        input_conn.commit()
    except Exception as e:
        # 有异常，回滚事务
        input_conn.rollback()

# 对 imei 进行处理，实现更新操作
imei_correct = "update day_value set imei = replace(imei, imei, substring(imei, -16, 15)) where length(imei) > 15;"
input_cursor.execute(imei_correct)
input_conn.commit()

input_cursor.close()
input_conn.close()
cursor.close()
conn.close()